﻿

create proc dbo.SaveUser
@UserID int = 0,
@Vorname nvarchar(50),
@Nachname nvarchar(50),
@LoginName nchar(10),
@Gesperrt bit,
@WorkingLevel tinyint,
@ValidTo Datetime2(7)

as

begin
	if @Vorname is null
	begin
		select -1 'Return', 'Vorname is null or empty.'
		goto WithError
	end

	if @Nachname is null
	begin
		select -1 'Return', 'Nachname is null or empty.'
		goto WithError
	end

	if @LoginName is null
	begin
		select -1 'Return', 'LoginName is null or empty.'
		goto WithError
	end

	if @Gesperrt is null
	begin
		select -1 'Return', 'Gesperrt is null or empty.'
		goto WithError
	end
	
	if @ValidTo is null
	begin
		select -1 'Return', 'ValidTo is null or empty.'
		goto WithError
	end

	begin tran

	;with sel as
	(
		select	@UserID			'UserID',
				@Vorname		'Vorname',
				@Nachname		'Nachname',
				@LoginName		'LoginName',
				@Gesperrt		'Gesperrt',
				@WorkingLevel	'WorkingLevel',
				@ValidTo		'ValidTo'
	) 
	merge into dbo.datUser as t
	using sel as s on
		s.UserID = t.UserID
	when not matched by target then
		insert (
			Vorname,
			Nachname,
			LoginName,
			Gesperrt,
			WorkingLevel,
			ValidTo
			)
		values (
			s.Vorname,
			s.Nachname,
			s.LoginName,
			s.Gesperrt,
			s.WorkingLevel,
			s.ValidTo
			)
	when matched then
		update set 
			Vorname			= s.Vorname,
			Nachname		= s.Nachname,
			LoginName		= s.LoginName,
			Gesperrt		= s.Gesperrt,
			WorkingLevel	= s.WorkingLevel,
			ValidTo			= s.ValidTo
		;
	commit tran
	return 0

WithError:
	if @@TRANCOUNT != 0
	begin
		rollback tran
	end
	return -1
end
GO
GRANT EXECUTE
    ON OBJECT::[dbo].[SaveUser] TO PUBLIC
    AS [dbo];

